﻿#region Header
// ------------------------ Licence / Copyright ------------------------
// 
// ScrumTable for Scrum Meeting Support
// Copyright © HSR - Hochschule für Technik Rapperswil
// All Rights Reserved
// 
// Author:
//  Michael Gfeller Silvan Gehrig Patrick Boos
// 
// ---------------------------------------------------------------------
#endregion

#region Usings

using System.Linq;
using ClosedXML.Excel;
using ScrumTable.Common.Collections;

#endregion

namespace ScrumTable.Common.Excel.OpenXmlSdk.Extensions
{
    /// <summary>
    /// Provides the additional functionalities required for Excel
    /// data management of a IXLWorksheet instance.
    /// </summary>
    public static class XLWorksheetUtil
    {
        #region Properties
        //--------------------------------------------------------------------
        // Properties
        //--------------------------------------------------------------------

        #endregion

        #region Methods
        //--------------------------------------------------------------------
        // Methods
        //--------------------------------------------------------------------

        /// <summary>
        /// Gets an empty row instance which can be used to fill additional
        /// values.
        /// </summary>
        /// <param name="thisPtr">Specifies the pointer to the IXLWorksheet instance.</param>
        public static IXLRow GetNextEmptyRow(this IXLWorksheet thisPtr)
        {
            PreCondition.AssertNotNull(thisPtr, "thisPtr");

            bool searchHeader = true;
            int rowNumber = 1;

            foreach (var row in thisPtr.Rows())
            {
                ++rowNumber;

                // search for the first data row which contains values
                if (searchHeader && row.AccommodatesValues())
                {
                    searchHeader = false;
                }
                else if (!row.AccommodatesValues())
                {
                    return row;
                }
            }
            return thisPtr.Row(rowNumber + 1);
        }

        /// <summary>
        /// Searches for a row entry which contains the specified value in
        /// the column with the given name.
        /// </summary>
        /// <param name="thisPtr">Specifies the pointer to the IXLWorksheet instance.</param>
        /// <param name="columnName">Specifies the name of the column to search for.</param>
        /// <param name="columnValue">Specifies the value of the column to search for.</param>
        /// <returns>Returns the first row or a null reference if the row could not be found.</returns>
        public static IXLRow SearchForXLRow(this IXLWorksheet thisPtr, string columnName, string columnValue)
        {
            PreCondition.AssertNotNull(thisPtr, "thisPtr");
            
            return (from dataCell in thisPtr.Range(columnName + "1", columnName + thisPtr.RowsUsed().Count()).Cells()
                    where ObjectUtil.ConvertToString(dataCell.Value) == columnValue || dataCell.FormulaR1C1 == columnValue
                    select thisPtr.Row(dataCell.Address.RowNumber)).FirstOrDefault();
        }

        #endregion
    }
}